package cubelabs.nexus.dao;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.StringTokenizer;

import org.hibernate.SQLQuery;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import cubelabs.nexus.entity.Session;

@Repository("sessionDAO")
public class SessionDAO extends BaseDAO<Session, Long>{
	
	public List<Session> findExpertTalks(){				
		List<Session>  sessionsList= findByCriteria(
				 Restrictions.and( Restrictions.ge("startTime", new Date()), Restrictions.eq("oneToMany",true)));
		return sessionsList;
	}
	
	public List<Session> findTalksByExpert(Long expertId) {
		List<Session>  sessionsList= findByCriteria(
				 Restrictions.and( Restrictions.eq("expert.id", expertId), Restrictions.eq("oneToMany",true)));
		return sessionsList;
	}
	public List<Session> findByExpert(Long expertId) {
		List<Session>  sessionsList= findByCriteria(
				 Restrictions.and(  Restrictions.eq("expert.id", expertId)));
		return sessionsList;
	}
        
//        SELECT s.* FROM
//SESSION s, USER u, category c, session_category sc 
//WHERE sc.session_id=s.id AND sc.category_id=c.id AND
//s.expert_id = u.id AND
//u.rating > rating AND
//s.startTime > dateStart AND
//s.startTime < dateEnd AND
        
        
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        
        public List<Session> searchExpertTalks(String categoryString, Integer cost,Integer rating,Date startDate, Date endDate, int limit){
            
            String queryStr="SELECT DISTINCT s.* FROM session s, session_category sc, category c ,  users u WHERE s.expert_id = u.id AND sc.session_id=s.id AND sc.category_id=c.id AND s.oneToMany=1 ";
            
            String categoryQuery= "";
            
            if(StringUtils.hasLength(categoryString)){
            	categoryQuery="AND (";
            	 StringTokenizer st = new StringTokenizer(categoryString,",");
                 while(st.hasMoreElements()){
                     String category=st.nextToken();
                     categoryQuery+=" c.name LIKE '%"+category+"%' ";
                     if(st.hasMoreElements()){
                         categoryQuery+=" OR ";
                     }else{
                         categoryQuery+=") ";
                     }
                  }
                 queryStr+=categoryQuery;
            }
            
            
            
            if(startDate!=null){
                queryStr+=" AND startTime > '"+sdf.format(startDate) + "'";
            }
            if(endDate!=null){
                queryStr+=" AND startTime < '"+sdf.format(endDate) + "'";
            }
            
            if(cost!=null){
                queryStr+=" AND u.price <= "+cost;
            }
            
            if(rating!=null){
                queryStr+=" AND u.rating >= "+rating;
            }
            queryStr += " Limit "+ limit;
            SQLQuery query =  (SQLQuery) getSession().createSQLQuery(queryStr);
            query.addEntity(getPersistentClass());
            List<Session> result = query.list();
            return result;
        }
        
          public List<Session> getCalnader(long userId,Date startDate, Date endDate){
            
            String queryStr="SELECT DISTINCT s.* FROM Session s WHERE "
                    + " s.user_id = "+userId+"  AND startTime > "+sdf.format(startDate)
                    +" AND startTime < "+sdf.format(endDate);
            
            SQLQuery query =  (SQLQuery) getSession().createSQLQuery(queryStr);
            query.addEntity(getPersistentClass());
            List<Session> result = query.list();
            return result;
        }
        

}
